package com.hok.misc.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;

public class ConnectDB {
	public static void main(String[] args) {
		//jdbc:oracle:thin:@//10.10.10.120:1521/ora1204
//		String dbUrl = "jdbc:oracle:thin:@//10.10.12.31:1521/pocdb";
//		String user = "zg";
//		String password = "zg";
//		connectDB5(dbUrl, user, password);
//		batchInsertToMysql();
//		batchInsertToOracle3();
//		batchInsertGprs();
		connectH("jdbc:oracle:thin:@ora120h.hz.asiainfo.com:1521:ora120h", "sd", "sd");
	}
	
	public static void connectH(String dbUrl, String user, String password) {
		Statement st = null;// ws_sus_keyword
		PreparedStatement pst = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(dbUrl, user, password);
			st = conn.createStatement();
			// conn.prepareStatement("insert into ws_sus_keyword values(?,?,?,?,?,?,?,?,?,?)");
			Calendar cal = Calendar.getInstance();
			rs = st.executeQuery("select sysdate,systimestamp from dual");
			if (rs.next()) {
				System.out.println(rs.getString(1));
				System.out.println(rs.getDate(1));
				System.out.println(rs.getDate(1, cal));
				
				System.out.println("-------------------------");
				
				System.out.println(rs.getString(2));
				System.out.println(rs.getDate(2));
				System.out.println(rs.getDate(2, cal));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}

	public static void connectDB5(String dbUrl, String user, String password) {
		Statement st = null;// ws_sus_keyword
		PreparedStatement pst = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(dbUrl, user, password);
			st = conn.createStatement();
			// conn.prepareStatement("insert into ws_sus_keyword values(?,?,?,?,?,?,?,?,?,?)");
			rs = st.executeQuery("select sysdate from dual");
			if (rs.next()) {
				System.err.println(rs.getString(1));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}
	
	public static void deleteGprs() {
		
	}
	public static void batchInsertGprs() {
		Connection connection = getOracleconnection();
		System.out.println(connection);
		Calendar time = Calendar.getInstance();
		time.set(Calendar.DAY_OF_MONTH, 29);
		String sql = "insert into usage_gprs(CHARGE_MODE,SESSION_CREATE_TIME,NOTIFY_TYPE,CG_ADDR) ";
		sql = sql + "values(?,?,?,?)";
		PreparedStatement prest = null;
		try {
			prest = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  
			connection.setAutoCommit(false);
			long start = System.currentTimeMillis();
			for (int i = 0; i < 100; i++) {
				Date date = new Date(time.getTimeInMillis());
				for(int j = 0; j < 2000; j++){  
					int v = i*100 + j;
					prest.setInt(1, j); prest.setDate(2, date);
					prest.setInt(3, 22); prest.setString(4, String.valueOf(v));
					prest.addBatch();  
				}  
				 prest.executeBatch();  
				 connection.commit();  
			}
			System.out.println("use:" + (System.currentTimeMillis() - start));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (prest != null) {
					prest.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void batchInsertToOracle3() {
		Connection connection = getOracleconnection();
		System.out.println(connection);
		Calendar time = Calendar.getInstance();
		time.set(Calendar.DAY_OF_MONTH, 29);
		String sql = "insert into USAGE_GSM(CHARGE_MODE,SESSION_CREATE_TIME,NOTIFY_TYPE,A_NUMBER) ";
		sql = sql + "values(?,?,?,?)";
		PreparedStatement prest = null;
		
		try {
			prest = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  
			connection.setAutoCommit(false);
			long start = System.currentTimeMillis();
			for (int i = 0; i < 10; i++) {
				Date date = new Date(time.getTimeInMillis());
				for(int j = 0; j < 100; j++){  
					int v = i*100 + j;
					prest.setInt(1, v); prest.setDate(2, date);
					prest.setInt(3, 22); prest.setString(4, String.valueOf(v));
					prest.addBatch();  
				}  
				 prest.executeBatch();  
				 connection.commit();  
			}
			System.out.println("use:" + (System.currentTimeMillis() - start));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (prest != null) {
					prest.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void batchInsertToOracle() {
		Connection connection = getOracleconnection();
		System.out.println(connection);
		Calendar time = Calendar.getInstance();
		time.set(Calendar.DAY_OF_MONTH, 28);
		String sql = "insert into USAGE_SMS(CHARGE_MODE,SESSION_CREATE_TIME,NOTIFY_TYPE) ";
		sql = sql + "values(?,?,?)";
		PreparedStatement prest = null;
		
		try {
			prest = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  
			connection.setAutoCommit(false);
			long start = System.currentTimeMillis();
			for (int i = 0; i < 10; i++) {
				Date date = new Date(time.getTimeInMillis());
				for(int j = 0; j < 100; j++){  
					int v = i*100+j;
					prest.setInt(1, v); prest.setDate(2, date);
					prest.setInt(3, 22); 
					prest.addBatch();  
				}  
				 prest.executeBatch();  
				 connection.commit();  
			}
			System.out.println("use:" + (System.currentTimeMillis() - start));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (prest != null) {
					prest.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void batchInsertToOracle1() {
		Connection connection = getOracleconnection();
		System.out.println(connection);
		String sql = "insert into DR_MMS_ZZ_20110225(dr_type,service_id,plan_id,acc_id,condition_id,user_number,user_type," +
				"start_time,RATING_RES,RATING_FLAG,RECORD_TYPE,ROAM_TYPE,SP_CODE,SERVICE_CODE,MM_TYPE,BILL_FLAG,APP_TYPE,INFO_TYPE," +
				"CARRY_TYPE,MESSAGE_LENGTH,ISMG_ID,LATE_LINK,INPUT_TIME,ROW_ID,SEQUENCE_NO) ";
		sql = sql + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement prest = null;
		try {
			prest = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  
			connection.setAutoCommit(false);
			long start = System.currentTimeMillis();
			for (int i = 0; i < 10; i++) {
				for(int j = 0; j < 10; j++){  
					int v = i*10+j;
					Date now = new Date(System.currentTimeMillis());
					prest.setInt(1, 2); prest.setInt(2, 1000); prest.setInt(3, 22);  prest.setInt(4, 2); prest.setInt(5, 222);  
					prest.setString(6, "18906501266"+v);prest.setInt(7, 66);prest.setDate(8, now);prest.setInt(9, 1);
					prest.setInt(10, i);prest.setInt(11, i);prest.setInt(12, i);prest.setString(13, "13");prest.setString(14, "14");
					prest.setInt(15, i);prest.setInt(16, i);prest.setInt(17, i);prest.setInt(18, i);prest.setInt(19, i);prest.setInt(20, i);
					prest.setString(21, "aa");prest.setInt(22, i);prest.setDate(23, now);prest.setInt(24, v);
					prest.setString(25, String.valueOf(v));
					prest.addBatch();  
				}  
				 prest.executeBatch();  
				 connection.commit();  
			}
			System.out.println("use:" + (System.currentTimeMillis() - start));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (prest != null) {
					prest.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void batchInsertToMysql() {
		Connection connection = getMySqlconnection();
		System.out.println(connection);
		String sql = "insert into usage_gsm(CHARGE_MODE,SESSION_CREATE_TIME,SESSION_UPDATE_TIME,FILE_CREATE_DATE,SEQUENCE_NO," +
		"SESSION_ID,REFUND_SESSION_ID,ORIGINAL_FILE,STOP_CAUSE,CHAEGEUP_SEQ) " +
		"values(?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement prest = null;
		try {
			prest = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  
			connection.setAutoCommit(false);
			long start = System.currentTimeMillis();
			for (int i = 0; i < 2000; i++) {
				for(int j = 0; j < 1000; j++){  
					prest.setInt(1, j);  
					prest.setDate(2, new Date(System.currentTimeMillis()));
					prest.setDate(3, new Date(System.currentTimeMillis()));  
					prest.setDate(4, new Date(System.currentTimeMillis()));  
					int v = i+j;
					prest.setInt(5, v);  
					prest.setString(6, String.valueOf(v));  
					prest.setString(7, String.valueOf(v));  
					prest.setString(8, String.valueOf(v));  
					prest.setInt(9, i);  
					prest.setString(10, String.valueOf(v));  
					prest.addBatch();  
				}  
				 prest.executeBatch();  
				 connection.commit();  
			}
			System.out.println("use:" + (System.currentTimeMillis() - start));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (prest != null) {
					prest.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
			
	}
	
	public static Connection getMySqlconnection() {
		String url = "jdbc:mysql://10.10.16.203:3306/ais";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(url, "root", "123456");
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static Connection getOracleconnection() {
		String url = "jdbc:oracle:thin:@//ora120f.hz.asiainfo.com:1521/ora120f";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection(url, "ud", "ud");
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	public static Connection getOracleconnection1() {
		String url = "jdbc:oracle:thin:@//ora120f.hz.asiainfo.com:1521/ora120f";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection(url, "jf", "jf");
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static Connection getOracleconnection2() {
		String url = "jdbc:oracle:thin:@//ora1201.hz.asiainfo.com:1521/ORA1201";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection(url, "xg", "xg");
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
